package com.infodms.dms.dao.sales.ordermanage.orderreport;

import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.infodms.dms.common.Constant;
import com.infodms.dms.dao.common.BaseDao;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;

public class DealerPriceQueryDAO extends BaseDao{
	public static Logger logger = Logger.getLogger(DealerPriceQueryDAO.class);
	private static final DealerPriceQueryDAO dao = new DealerPriceQueryDAO ();
	public static final DealerPriceQueryDAO getInstance() {
		return dao;
	}
	
	public static PageResult<Map<String, Object>> getDealerPriceList(
			String dealerId, String modelCode, int pageSize, int curPage) {
		StringBuffer sql = new StringBuffer("");

		sql.append("SELECT G1.GROUP_CODE MODEL_CODE,\n");
		sql.append("       G1.GROUP_NAME MODEL_NAME,\n");
		sql.append("       G.GROUP_CODE,\n");
		sql.append("       G.GROUP_NAME,\n");
		sql.append("       D.SALES_PRICE,\n");
		sql.append("       TO_CHAR(D.CREATE_DATE, 'yyyy-MM-dd') CREATE_DATE\n");
		sql.append("  FROM (select dpr1.price_id price_id\n");
		sql.append("         from vw_TM_DEALER_PRICE_RELATION dpr1\n");
		sql.append("        where trunc(dpr1.create_date) =\n");
		sql.append("              (select max(trunc(dpr2.create_date))\n");
		sql.append("                 from vw_TM_DEALER_PRICE_RELATION dpr2\n");
		sql.append("                where dpr2.dealer_id IN (" + dealerId+ "))\n");
		sql.append("          and dpr1.dealer_id IN (" + dealerId + ")) R,\n");
		sql.append("       vw_TT_VS_PRICE_DTL          D,\n");
		sql.append("       TM_VHCL_MATERIAL_GROUP   G,\n");
		sql.append("       TM_VHCL_MATERIAL_GROUP   G1\n");
		sql.append(" WHERE R.PRICE_ID = D.PRICE_ID\n");
		sql.append("   AND D.GROUP_ID = G.GROUP_ID\n");
		sql.append("   AND G.PARENT_GROUP_ID = G1.GROUP_ID\n");
		// sql.append("   AND R.DEALER_ID IN ("+dealerId+")\n");
		if (!"".equals(modelCode)) {
			sql.append("   AND G1.GROUP_CODE LIKE ('%" + modelCode.trim()
					+ "%')\n");
		}

		// sql.append("   AND R.IS_DEFAULT = "+Constant.IF_TYPE_YES+"\n");
		sql.append(" ORDER BY MODEL_CODE, GROUP_CODE\n");
		return dao.pageQuery(sql.toString(), null, dao.getFunName(), pageSize,
				curPage);
	}
	
	public static PageResult <Map<String,Object>> getDealerPriceListOEM(String dealerCode,String modelCode, int pageSize,int curPage){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT TMD.DEALER_SHORTNAME,\n");
		sql.append("       TMD.DEALER_CODE,\n");
		sql.append("       G1.GROUP_CODE MODEL_CODE,\n");  
		sql.append("       G1.GROUP_NAME MODEL_NAME,\n");  
		sql.append("       G.GROUP_CODE,\n");  
		sql.append("       G.GROUP_NAME,\n");  
		sql.append("       D.SALES_PRICE,\n");  
		sql.append("       TO_CHAR(D.CREATE_DATE, 'yyyy-MM-dd') CREATE_DATE\n");  
		sql.append("  FROM (select dpr1.price_id price_id,dpr1.dealer_id dealer_id\n");
		sql.append("         from vw_TM_DEALER_PRICE_RELATION dpr1\n");
		sql.append("        where trunc(dpr1.create_date) =\n");
		sql.append("              (select max(trunc(dpr2.create_date))\n");
		if (!"".equals(dealerCode)) {
			String[] array = dealerCode.split(",");
			StringBuffer strDealerCode = new StringBuffer("");
			for (int i = 0; i < array.length; i++) {
				strDealerCode.append("'" + array[i] + "'");
				if (i != array.length - 1) {
					strDealerCode.append(",");
				}
			}
			sql.append("                 from vw_TM_DEALER_PRICE_RELATION dpr2\n");
			sql.append("                where dpr2.dealer_id in (select d2.dealer_id from tm_dealer d2 where d2.dealer_code in (+");
			sql.append(strDealerCode);
			sql.append(")))\n");
			sql.append("          and dpr1.dealer_id in (select d1.dealer_id from tm_dealer d1 where d1.dealer_code in (+");
			sql.append(strDealerCode);
			sql.append("))) R,\n");
		} else {
			sql.append("                 from vw_TM_DEALER_PRICE_RELATION dpr2)) R,\n");
		}
		sql.append("       vw_TT_VS_PRICE_DTL          D,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP   G,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP   G1,\n");  
		sql.append("       TM_DEALER                TMD\n");  
		sql.append(" WHERE R.PRICE_ID = D.PRICE_ID\n");  
		sql.append("   AND D.GROUP_ID = G.GROUP_ID\n");  
		sql.append("   AND G.PARENT_GROUP_ID = G1.GROUP_ID\n");  
		sql.append("   AND R.DEALER_ID = TMD.DEALER_ID\n");  
		if (!"".equals(dealerCode)) {
			String[] array = dealerCode.split(",");
			sql.append("   AND TMD.DEALER_CODE IN (\n");
			for (int i = 0; i < array.length; i++) {
				sql.append("'" + array[i] + "'");
				if (i != array.length - 1) {
					sql.append(",");
				}
			}
			sql.append(")\n");

		}
		if (!"".equals(modelCode)) {
			sql.append("   AND G1.GROUP_CODE LIKE ('%"+modelCode.trim()+"%')\n");
		}
		//sql.append("   AND R.IS_DEFAULT = "+Constant.IF_TYPE_YES+"\n");
		sql.append(" ORDER BY MODEL_CODE, GROUP_CODE\n");
		return dao.pageQuery(sql.toString(), null,dao.getFunName(),pageSize ,curPage );
	}

	public static List<Map<String,Object>> getLowerDealerList(String dealerId){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT TMD.DEALER_SHORTNAME,TMD.DEALER_CODE, TMD.DEALER_ID\n");
		sql.append("  FROM TM_DEALER TMD\n");  
		sql.append(" WHERE TMD.PARENT_DEALER_D IN ("+dealerId+")\n");  
		sql.append(" ORDER BY TMD.DEALER_CODE\n");
		return dao.pageQuery(sql.toString(), null,dao.getFunName());
	}
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}
}
